package serverapp;

import java.io.*;
import java.net.*;
import java.util.StringTokenizer;
import java.sql.*;

public final class ServerApp
{
	private static String SQLArray[][]; // [Col][Row]
	// STEP 1 a1    
    // Server socket
    private ServerSocket listener;
    
    // STEP 1 a2 Client connection
    private Socket client;
    
    ObjectInputStream in;
    ObjectOutputStream out;
    
    /** Creates a new instance of ServerApp */
    public ServerApp()
    {
    	createConnection();
    }
    
    public void createConnection()
    {
    	// Create server socket
        try 
        {
			// STEP 1 b
            listener = new ServerSocket(12345, 10);
        }
        catch (IOException ioe)
        {
         	System.out.println("IO Exception - in createConnection(): " + ioe.getMessage());          	
        }
    }
    
    public void listen()
    {
        // Start listening for client connections
		// STEP 2
        try 
        {
        	System.out.println("Server: waiting for client to connect..."); 
        	client = listener.accept();
        	String IPAddress = "" + client.getInetAddress();
        	System.out.println("Server: connected to client " + IPAddress.substring(1,IPAddress.length()));

      		// STEP 3
          	processClient();
        }
        catch(IOException ioe)
        {
            System.out.println("IO Exception - in listen(): " + ioe.getMessage());
        }
    }
	// STEP 3 DEFINED    
    public void processClient()
    {
    	ResultSet rs;
    	String statusResponse[][] = new String[1][1];
        // Communicate with the client
        try 
        {
			//  STEP 3 a
            out = new ObjectOutputStream(client.getOutputStream());
            out.flush();

			// STEP 3 b
            in = new ObjectInputStream(client.getInputStream());
            
			// STEP 4 PROCESSING PHASE
			String msgIn;
			StringTokenizer st;
			final double RentDVDCost = 15;//cost for renting a movie
			do
			{
				String msgOut = "";
				System.out.println("Server: waiting for a message from the client...");
            	msgIn = (String)in.readObject();
            	
            	st = new StringTokenizer(msgIn, "#");
                        	
            	System.out.println("Client says: " + msgIn);
            	
            	System.out.println("Server: starting to process the message...");
            	
            	String firstToken = "";
            	
            	if (st.hasMoreTokens())
            	{
            		firstToken = st.nextToken();
            	}
            	/*Client sends AddCustomer string along with the customer details to the server 
            	  and the server sends adds the customer to the database*/
            	if (firstToken.equals("AddCustomer")) // Stuart
            	{
            		System.out.println("\nClient wants to add a Customer");            		
            		Customer cust = new Customer();
            		cust.setName(st.nextToken());
            		cust.setSurname(st.nextToken());
            		cust.setPhoneNum(st.nextToken());
            		cust.setCredit(Double.parseDouble(st.nextToken()));
            		Database.insertCustomer( cust.getPhoneNum(), cust.getSurname(), cust.getName(), cust.getCredit());
            		statusResponse[0][0] = "Customer insert Successful";           		
            		out.writeObject(statusResponse);
            	}
            	/*Movie details and the string AddMovie are sent to the server by the client the new movie
            	  is added to the database*/      	
            	else if (firstToken.equals("AddMovie")) // Stuart
            	{
            		System.out.println("\nClient wants to add a Movie");            		
            		Database.insertDVD(st.nextToken(), st.nextToken(), Boolean.parseBoolean(st.nextToken()), true);
            		statusResponse[0][0] = "DVD insert Successful";           		
            		out.writeObject(statusResponse);  		
            	}
            	else if (firstToken.equals("DeleteCustomer1"))
            	{
            		System.out.println("Client wants to load DeleteCustomer combobox");
            		int arraySize = Database.getSize("Select count(*) from Customer;");
            		rs = Database.selectQuery("Select PhoneNum, Surname FROM Customer;");
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	/*AddMovie string is sent to the server by the client to retrieve Dvd code and title from the database
            	 *witch are sent back to client and the client populate the delete combobox*/
            	else if (firstToken.equals("DeleteMovie1"))
            	{
            		System.out.println("Client wants to load DeleteMovies combobox");
            		int arraySize = Database.getSize("Select count(*) from dvd;");
            		rs = Database.selectQuery("Select DVDCode, Title FROM DVD;");
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	/*When the user selects a dvd to delete, the client sends the string DeleteMovie2 And the dvd code to the server*/
            	else if (firstToken.equals("DeleteMovie2"))
            	{ //server deletes selected movie, unless it is out and sends message
            		System.out.println("Client wants to delete selected Movie");
            		String selectedMovie = st.nextToken();
            		rs = Database.selectQuery("Select Available FROM DVD WHERE DVDCode = "+selectedMovie+";");//look up available
            		rs.next();
            		boolean isAvailable = rs.getBoolean(1);
            		System.out.println("The available status of DVD is: "+isAvailable);
            		statusResponse = new String[1][1];
            	/*if the selected dvd is available then it is deleted by the server*/
            		if(isAvailable) //delete movie
            		{	
            			Database.deleteDVD(selectedMovie);
            			System.out.println("Block: 1, movie deleted");
            			statusResponse[0][0] = "#InDvd";           		
            			out.writeObject(statusResponse);
            		}
            		else //send confirmation message to client
            		{ /*if not available then message #outDvd is sent to the client*/
            			System.out.println("Block: 2, movie can't be deleted yet");
            			statusResponse[0][0] = "#OutDvd";           		
            			out.writeObject(statusResponse);
            		}
            	}
            	else if (firstToken.equals("DeleteMovie3"))
            	{ //if the user wants to delete a dvd that is out then dvd is deleted
            		System.out.println("Client wants to delete selected Movie that is out");
            		String selectedMovie = st.nextToken();
            		
        			Database.deleteDVD(selectedMovie);
        			statusResponse = new String[1][1];
        			statusResponse[0][0] = "Movie has been deleted";           		
        			out.writeObject(statusResponse);
            	}
            	/*The Server recieces the string DeleteCustomer1 from the client and retrieve all the customer phoneNumber and 
            	 *the customer surname from the Customer table and send the to the client*/
            	else if (firstToken.equals("DeleteCustomer1"))
            	{
            		System.out.println("Client wants to load DeleteCustomer combobox");
            		int arraySize = Database.getSize("Select count(*) from Customer;");
            		rs = Database.selectQuery("Select PhoneNum, Surname FROM Customer;");
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	/*DeleteCustomer2 and the customer phoneNumber string is recieved from the cleint*/ 
            	else if (firstToken.equals("DeleteCustomer2"))
            	{
            		System.out.println("Client wants to delete selected Customer");
            		String selectedCustomer = st.nextToken();
            		statusResponse = new String[1][1];
            		int numDVDOut = Database.getSize("Select count(*) from DVD WHERE PhoneNum = '"+selectedCustomer+"';");
            		System.out.println("customer has "+numDVDOut+" DVD's out");
            		
            		/*if customer has no dvds the customer is deleted from the customer table*/
        			if(numDVDOut == 0)
        			{ //delete DVD
	        			Database.deleteCustomer(selectedCustomer);
	        			statusResponse[0][0] = "Customer has been deleted";           		
	        			out.writeObject(statusResponse);
	        		}
	        		else //warn client that Customer has dvd's out
	        		{/*if customer has dvd's out the string #hasDvd is sent to the client to indicate that
	        		 the customer has dvds out*/
            			statusResponse[0][0] = "#hasDvd";     		
            			out.writeObject(statusResponse);
	        		}
            	}
            	
            	else if (firstToken.equals("DeleteCustomer3"))//delete Customer and his DVDs taken out
            	{
            		System.out.println("Client wants to delete selected Customer and the DVDs taken out");
            		String selectedCustomer = st.nextToken();
            		statusResponse = new String[1][1];
            		
            		Database.deleteAllMoviesTakenOutByCustomer(selectedCustomer);
        			Database.deleteCustomer(selectedCustomer);//delete customer
        			statusResponse[0][0] = "Customer and the DVD's he has, have been deleted";           		
        			out.writeObject(statusResponse);
            	}
            
            	else if (firstToken.equals("EditCustomer1")) // Stuart - EditCustomer1
            	{
            		System.out.println("Client wants to edit a Customer part 1");
            		int arraySize = Database.getSize("Select count(*) from Customer;");
            		rs = Database.selectQuery("Select PhoneNum FROM Customer;");
            		build2DArray(rs, arraySize);
            		out.writeObject(SQLArray);
            	}
            	
            	else if (firstToken.equals("EditCustomer2")) // Stuart - EditCustomer2#phoneNum
            	{
            		System.out.println("Client wants to edit a Customer part 2");
            		String phoneNum = st.nextToken();
            		int arraySize = Database.findCustomerCount(phoneNum);
            		rs = Database.findCustomerQuery(phoneNum);
            		build2DArray(rs, arraySize);
            		out.writeObject(SQLArray);
            	}
            	
            	else if (firstToken.equals("EditCustomer3")) // Stuart - EditCustomer3#oldNum#Name#Surname#newPhoneNum
            	{
            		// From the crashed PC
            		System.out.println("Client wants to edit a Customer part 3");
            		Customer cust = new Customer();
            		String oldNum = st.nextToken();
            		System.out.println("oldNum = " + oldNum);
            		cust.setName(st.nextToken());
            		cust.setSurname(st.nextToken());
            		cust.setPhoneNum(st.nextToken());
            		statusResponse = new String[1][1];
            		if (oldNum.equals(cust.getPhoneNum())) // if the phone number has not been changed
            		{
            			System.out.println("In the phone number has NOT been changed");
            			System.out.println("\nBefore crash.");
            			//
            			Database.updateCustomerNotNumber(cust);
            			//Database.updateCustomer(oldNum, cust);
            			//
            			System.out.println("\nAfter Crash");
            			statusResponse[0][0] = "Customer updated successfully with no PhoneNumber change";
            		}
            		
            		else // if the phone number has been changed
            		{
            			System.out.println("In the phone number HAS been changed");
            			int custSize = Database.findCustomerCount(cust.getPhoneNum());
            			System.out.println("custSize = " + custSize);
            			if (custSize == 0) // if the new phone number is unique
            			{
            				System.out.println("In the phone number IS unique changed");
            				int arraySize = Database.findDVDsOutByCustomerCount(oldNum);
	            			if (arraySize != 0) // if the customer has DVDs out...
	            			{
	            				System.out.println("The Customer has DVDs out");
	            				rs = Database.findDVDsOutByCustomerQuery(oldNum);
		            			build2DArray(rs, arraySize);
		            			String DVDOutCode[][] = SQLArray;// store OutDVDs into Temp array
		            			
		            			for (int i = 0; i < DVDOutCode.length; i++)
		            			{
		            				System.out.println("DVD Code: " + DVDOutCode[i][0]);
		            			}
		            			
		            			// remove DVDs out by customer (return DVDs)
		            			System.out.println("About to remove DVDs out by customer...");
		            			for (int k = 0; k < DVDOutCode.length; k++)
		            			{
		            				Database.updateDVD(DVDOutCode[k][0]); // Markus
		            				System.out.println("Returned DVD: " + DVDOutCode[k][0]);
		            			}
		            			statusResponse[0][0] = "Customer successfully updated, and DVDs out have been reallocated";
		            			// Change Customer PhoneNum (and all other details)
		            			Database.updateCustomer(oldNum, cust);
		            			
		            			System.out.println("Past Change Customer PhoneNum");
		            			// Rerent the DVDs with the new customer PhoneNum
		            			
		            			for (int j = 0; j < DVDOutCode.length; j++)
		            			{
		            				Database.updateQuery("UPDATE DVD SET Available = false, PhoneNum = '" + cust.getPhoneNum() + "' WHERE DVDCode = " + DVDOutCode[j][0] + ";"); // Markus
		            			}
	            			}
	            			else // if the customer has no DVDs Out
	            			{
	            				System.out.println("The Customer has NO DVDs out");
	            				// Change Customer PhoneNum (and all other details)
		            			Database.updateCustomer(oldNum, cust);
		            			statusResponse[0][0] = "Customer has no DVDs out and updated successfully";
	            			}
            			}
            			else // if the phone number is not unique
            			{
            				System.out.println("The phoneNumber is NOT unique");
            				statusResponse[0][0] = "Customer phone number must be a unique phone number";
            			}
            		}
            		System.out.println("About to send to client: " + statusResponse[0][0]);
            		out.writeObject(statusResponse);
            	}
            	
            	else if (firstToken.equals("ViewAvailableMovies")) // Stuart
            	{
            		System.out.println("Client wants to view Available Movies");
            		int arraySize = Database.getSize("Select count(*) from DVD where available = true;");
            		rs = Database.selectQuery("Select DVDCode, Title, Category, NewRelease, Available FROM DVD where available = true ORDER BY Title;");            		
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	
           		else if (firstToken.equals("ViewCustomers"))//all customers
            	{
            		System.out.println("Client wants to view All Customers");
            		int arraySize = Database.getSize("Select count(*) from Customer;");
            		rs = Database.selectQuery("Select * FROM Customer ORDER BY Surname, FirstName");            		
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);	
            	}
            	
            	else if (firstToken.equals("ViewMoviesStartingWith"))//all customers
            	{
            		System.out.println("Client wants to view Movies starting with entered text");
            		String searchStr;
            		
            		int arraySize = 0;
            		if(st.hasMoreTokens())
            		{
            			searchStr = st.nextToken();
            			System.out.println("\nSearch movies starting with: "+searchStr);
            			arraySize = Database.getMoviesStartWithSize(searchStr);
            			System.out.println("\n\nMovies Starting with "+searchStr+" size: "+arraySize);
            			rs = Database.selectMoviesStartWithQuery(searchStr);
            		}
            		else
            		{
            			System.out.println("\nSearch movies starting with: ");
            			arraySize = Database.getSize("Select count(*) FROM DVD;");
            			rs = Database.selectQuery("Select DVDCode, Title, Category, NewRelease, Available FROM DVD ORDER BY Title;");
            		}
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);	
            	}
            	            	
            	else if (firstToken.equals("ViewMoviesNotAvailable"))//all movies that are out
            	{
            		System.out.println("Client wants to view Movies that are out");           		
            		int arraySize = Database.getSize("Select count(*) from dvd where Available = false;");            		
            		rs = Database.selectQuery("Select DVD.Title, Customer.Surname, Customer.PhoneNum FROM DVD, Customer WHERE Customer.phoneNum = DVD.phoneNum AND DVD.Available = false ORDER BY DVD.Title;");           		
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	
            	else if (firstToken.equals("ViewAllMovies"))//ViewAllMoviesMenu
            	{
            		System.out.println("Client wants to view all Movies");           		
            		int arraySize = Database.getSize("Select count(*) from dvd;");            		
            		rs = Database.selectQuery("select DVDCode, Title, Category, NewRelease, Available from DVD ORDER BY Title;");
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	else if (firstToken.equals("RentMovieF1"))
            	{
            		System.out.println("Client wants to fill RentMovieForm customer phoneNum combobox");           		
            		int arraySize = Database.getSize("Select count(*) from Customer;");            		
            		rs = Database.selectQuery("Select PhoneNum from Customer;");
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}            	
            	else if (firstToken.equals("RentMovieF2"))//return available movies for combobox
            	{
            		System.out.println("Client wants to fill RentMovieForm available DVD combobox");           		
            		int arraySize = Database.getSize("Select count(*) from DVD WHERE Available = true;");            		
            		rs = Database.selectQuery("Select DVDCode, Title from DVD WHERE Available = true;");
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	else if (firstToken.equals("RentMovie"))//update - dvd is out
            	{
            		System.out.println("Client wants to rent the selected movie");
            		String custPhoneNum = st.nextToken();
            		rs = Database.selectQuery("Select Credit from Customer WHERE PhoneNum = '" + custPhoneNum + "';");
            		rs.next();
            		double credit = Double.parseDouble(rs.getString(1));//select credit
            		statusResponse = new String[1][1];
            		
            		if (credit >= RentDVDCost)//has enough credit
            		{
            			Database.updateQuery("UPDATE DVD SET Available = false, PhoneNum = '" + custPhoneNum + "' WHERE DVDCode = " + st.nextToken() + ";");
            			Database.updateQuery("UPDATE Customer SET Credit = "+ (credit - RentDVDCost) +" WHERE PhoneNum = '" + custPhoneNum + "';");
            			statusResponse[0][0] = "#Movie rented out";
            		}
            		else
            			statusResponse[0][0] = "#Selected customer does not have enough credit.";
            		out.writeObject(statusResponse);
            	}
            	
            	else if(firstToken.equals("ReturnMovie1"))
            	{
            		System.out.println("Client wants to fill ReturnMovieForm combobox");           		
            		int arraySize = Database.getSize("Select count(*) from dvd WHERE Available = false;");            		
            		rs = Database.selectQuery("select DVDCode, Title from DVD WHERE Available = false;");
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	else if (firstToken.equals("ReturnMovie2"))//update - dvd is available
            	{
            		System.out.println("Client wants to return selected movie");
            		Database.updateQuery("UPDATE DVD SET Available = true, PhoneNum = NULL WHERE DVDCode = " + st.nextToken() + ";");
            		statusResponse[0][0] = "Movie has been returned";           		
            		out.writeObject(statusResponse);
            	}            	
            	else if (firstToken.equals("AddCredit1")) // Stuart
            	{
            		System.out.println("Client wants to add Credit");           		
            		int arraySize = Database.getSize("Select count(*) from Customer;");            		
            		rs = Database.selectQuery("select PhoneNum, Surname from Customer;");
            		build2DArray(rs, arraySize);            		
            		out.writeObject(SQLArray);
            	}
            	
            	else if (firstToken.equals("AddCredit2")) // Stuart
            	{
            		Customer cust = new Customer();
            		cust.setPhoneNum(st.nextToken());
            		cust.setCredit(Double.parseDouble(st.nextToken()));
            		System.out.println("UPDATE Customer SET Credit = (Credit + " + cust.getCredit() + ") WHERE PhoneNum = " + '"' + cust.getPhoneNum() + '"' + ";");
            		Database.addCreditToCustomer(cust.getPhoneNum(), cust.getCredit());
            		statusResponse[0][0] = "Customer insert Successful";           		
            		out.writeObject(statusResponse);
            	}
            	
            	else if (msgIn.equals("bye"))
            	{
            		 System.out.println("Message from client is: bye");
            	}
            	
            	else
            	{
            		String ErrorMsg = "Server: message from client not understood.";
            		System.out.println(ErrorMsg);
            		SQLArray = new String[1][1];
            		SQLArray[0][0] = ErrorMsg;
            		out.writeObject(SQLArray);
            	}
            	     	
				System.out.println("Server: message processing complete.");
           		out.flush();
			}
			while(!(msgIn.equals("bye")));            
            // STEP 5 CLOSE CONNECTION
            System.out.println("Server: closing from bye message");
            out.close();
            in.close();
            client.close();
        }
        catch (IOException ioe)
        {
            System.out.println("Connection dropped by client."); // \nIO Exception - caught in processClient(): " + ioe.getMessage());
            System.out.println("Now trying to reconnect... ");
            try
            {
           		listener.close();
            	client.close();
            	createConnection();
            	listen();
            }
            catch (Exception e)
            {
            	System.out.println("Error in trying to reconnect:" + e.getMessage());
            }                       
        }
        catch (ClassNotFoundException cnfe)
        {
            System.out.println("Class not found: " + cnfe.getMessage());
        }
        catch (Exception e)
        {
            System.out.println("Exception: " + e.getMessage());
        }
    }
    
    public static void build2DArray(ResultSet rs, int arraySize)
    {
    	try
    	{
    		ResultSetMetaData rsmd = rs.getMetaData();

			System.out.println("Server requested size and received: " + arraySize);
			
			if (rs.next())
			{
				SQLArray = new String[arraySize][rsmd.getColumnCount()];
				for (int Row = 0; Row < arraySize; Row++)
				{
	    			for (int Col = 0; Col < rsmd.getColumnCount(); Col++)
	    			{
	    				SQLArray[Row][Col] = new String();
	    				SQLArray[Row][Col] = rs.getString(Col+1);
	    				System.out.print(SQLArray[Row][Col] + " ");
	    			}
	    			System.out.println();
	    			rs.next();
				}           		
			}
			else
			{
				SQLArray = new String[1][1];
				SQLArray[0][0] = "#No Records";
			}
    	}
    	catch (Exception e)
    	{
    		System.out.println("Error in build2DArray(): " + e.getMessage());	
    	}    	
    }
    
    public static void main(String[] args)
    {
    	Database.initialize();
    	//Database.createTables();
    	//SerializedFiles.loadCustomerTable();
    	//SerializedFiles.loadDVDTable();
    	
        ServerApp server = new ServerApp();
        // Start waiting for a connection
        server.listen();
        //may need to move functions below
        //SerializedFiles.saveDVDTable(Database.createDvdArray());
        //drop tables
    }
}